package com.etime.shycourse.dao;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.etime.shycourse.dto.AdLogDto;
import com.etime.shycourse.pojo.AdLog;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import java.util.Date;

/**
 * 广告统计Mapper
 */
@Repository
public interface AdStatisticMapper extends BaseMapper<AdLog> {

    /**
     * 两表联查正在进行的广告列表
     * @return
     */
    @Select("SELECT adlog.adid,adlog.placeid,adlog.adimg,adlog.adcourseid,adlog.adlink,adlog.adstart,adlog.adend,adlog.adprice,adlog.adsumprice,adlog.adsort,adlog.adstate,course.coursetitle FROM adlog " +
            "LEFT OUTER JOIN course " +
            "on adlog.adcourseid=course.courseid" +
            " where adlog.adstart < #{date} and adlog.adend > #{date}")
    Page<AdLogDto> selectProgressList(Page<AdLogDto> page, @Param("date") Date date);

    /**
     * 两表联查已经结束的广告列表
     * @return
     */
    @Select("SELECT adlog.adid,adlog.placeid,adlog.adimg,adlog.adcourseid,adlog.adlink,adlog.adstart,adlog.adend,adlog.adprice,adlog.adsumprice,adlog.adsort,adlog.adstate,course.coursetitle FROM adlog " +
            "LEFT OUTER JOIN course " +
            "on adlog.adcourseid=course.courseid" +
            " where adlog.adend < #{date}")
    Page<AdLogDto> selectClosedList(Page<AdLogDto> page, @Param("date") Date date);

    /**
     * 两表联查等待投放的广告列表
     * @return
     */
    @Select("SELECT adlog.adid,adlog.placeid,adlog.adimg,adlog.adcourseid,adlog.adlink,adlog.adstart,adlog.adend,adlog.adprice,adlog.adsumprice,adlog.adsort,adlog.adstate,course.coursetitle FROM adlog " +
            "LEFT OUTER JOIN course " +
            "on adlog.adcourseid=course.courseid" +
            " where adlog.adstart > #{date}")
    Page<AdLogDto> selectWaitPutList(Page<AdLogDto> page,@Param("date") Date date);

    /**
     * 根据placeid联表查询广告位置
     * @param placeid
     * @return
     */
    @Select("SELECT DISTINCT adplace.placename " +
            "from adplace LEFT JOIN adlog " +
            "on adlog.placeid = adplace.placeid " +
            "where adlog.placeid=#{placeid}")
    String selectPlace(@Param("placeid") Long placeid);

    /**
     * 获取某个广告位的广告总收益
     * @param placeid
     * @return
     */
    @Select("SELECT sum(adsumprice) " +
            "from adlog " +
            "WHERE placeid=#{placeid}")
    Double selectTotalAdRevenue(@Param("placeid") Long placeid);

    /**
     * 根据placeid联表查询正在进行的广告列表
     * @param placeid
     * @param date
     * @return
     */
    @Select("SELECT adlog.adid,adlog.placeid,adlog.adimg,adlog.adcourseid,adlog.adlink,adlog.adstart,adlog.adend,adlog.adprice,adlog.adsumprice,adlog.adsort,adlog.adstate,course.coursetitle,adplace.placename "+
    "FROM adplace " +
            "LEFT OUTER  JOIN adlog ON adlog.placeid = adplace.placeid " +
            "LEFT OUTER  JOIN course ON course.courseid = adlog.adcourseid " +
            "WHERE adlog.placeid=#{placeid} and adlog.adstart < #{date} and adlog.adend > #{date} ")
    Page<AdLogDto> selectProgressListByPlaceid(Page<AdLogDto> page,Long placeid, Date date);
    /**
     * 根据placeid联表查询等待投放的广告列表
     * @param placeid
     * @param date
     * @return
     */
    @Select("SELECT adlog.adid,adlog.placeid,adlog.adimg,adlog.adcourseid,adlog.adlink,adlog.adstart,adlog.adend,adlog.adprice,adlog.adsumprice,adlog.adsort,adlog.adstate,course.coursetitle,adplace.placename "+
            "FROM adplace " +
            "LEFT OUTER  JOIN adlog ON adlog.placeid = adplace.placeid " +
            "LEFT OUTER  JOIN course ON course.courseid = adlog.adcourseid " +
            "WHERE adlog.placeid=#{placeid} and adlog.adstart > #{date} ")
    Page<AdLogDto> selectWaitPutListByPlaceid(Page<AdLogDto> page,Long placeid, Date date);
    /**
     * 根据placeid联表查询已经结束的广告列表
     * @param placeid
     * @param date
     * @return
     */
    @Select("SELECT adlog.adid,adlog.placeid,adlog.adimg,adlog.adcourseid,adlog.adlink,adlog.adstart,adlog.adend,adlog.adprice,adlog.adsumprice,adlog.adsort,adlog.adstate,course.coursetitle,adplace.placename "+
            "FROM adplace " +
            "LEFT OUTER  JOIN adlog ON adlog.placeid = adplace.placeid " +
            "LEFT OUTER  JOIN course ON course.courseid = adlog.adcourseid " +
            "WHERE adlog.placeid=#{placeid} and adlog.adend < #{date}")
    Page<AdLogDto> selectClosedListByPlaceid(Page<AdLogDto> page,Long placeid, Date date);

    /**
     * 根据orgid三表联查机构名称
     * @param orgid
     * @return
     */
    @Select("SELECT DISTINCT organization.orgname FROM adlog " +
            "LEFT OUTER JOIN  course on adlog.adcourseid=course.courseid  " +
            "LEFT OUTER JOIN  organization on course.courseorgid=organization.orgid " +
            "WHERE organization.orgid=#{orgid}")
    String selectOrgName(@Param("orgid")Integer orgid);

    /**
     * 根据orgid三表联查总广告数量
     * @param orgid
     * @return
     */
    @Select("SELECT COUNT(adid) FROM adlog " +
            "LEFT OUTER JOIN  course on adlog.adcourseid=course.courseid  " +
            "LEFT OUTER JOIN  organization on course.courseorgid=organization.orgid " +
            "WHERE organization.orgid=#{orgid}")
    Integer selectAdTotalNum(@Param("orgid")Integer orgid);

    /**
     * 根据orgid三表联查正在进行的广告数量
     * @param orgid
     * @return
     */
    @Select("SELECT COUNT(adid) FROM adlog " +
            "LEFT OUTER JOIN  course on adlog.adcourseid=course.courseid  " +
            "LEFT OUTER JOIN  organization on course.courseorgid=organization.orgid " +
            "WHERE organization.orgid=#{orgid} and adlog.adstart < #{date} and adlog.adend > #{date}")
    Integer selectProgressAdNum(@Param("orgid")Integer orgid, @Param("date")Date date);

    /**
     * 根据orgid三表联查正在进行的广告列表
     * @param
     * @return
     */
    @Select("SELECT DISTINCT adlog.adid,adlog.placeid,adlog.adimg,adlog.adcourseid,adlog.adlink,adlog.adstart,adlog.adend,adlog.adprice,adlog.adsumprice,adlog.adsort,adlog.adstate,organization.orgname,course.coursetitle FROM adlog " +
            "LEFT OUTER JOIN  course on adlog.adcourseid=course.courseid  " +
            "LEFT OUTER JOIN  organization on course.courseorgid=organization.orgid " +
            "WHERE organization.orgid=#{orgid} and adlog.adstart < #{date} and adlog.adend > #{date}")
    Page<AdLogDto> selectProgressAdList(Page<AdLogDto> page,@Param("orgid")Integer orgid, @Param("date")Date date);

    /**
     * 根据orgid三表联查待投放广告数量
     * @param orgid
     * @return
     */
    @Select("SELECT COUNT(adid) FROM adlog " +
            "LEFT OUTER JOIN  course on adlog.adcourseid=course.courseid  " +
            "LEFT OUTER JOIN  organization on course.courseorgid=organization.orgid " +
            "WHERE organization.orgid=#{orgid} and adlog.adstart > #{date}")
    Integer selectWaitPutAdNum(@Param("orgid")Integer orgid, @Param("date")Date date);

    /**
     * 根据orgid三表联查待投放广告列表
     * @param
     * @return
     */
    @Select("SELECT DISTINCT adlog.adid,adlog.placeid,adlog.adimg,adlog.adcourseid,adlog.adlink,adlog.adstart,adlog.adend,adlog.adprice,adlog.adsumprice,adlog.adsort,adlog.adstate,organization.orgname,course.coursetitle FROM adlog " +
            "LEFT OUTER JOIN  course on adlog.adcourseid=course.courseid  " +
            "LEFT OUTER JOIN  organization on course.courseorgid=organization.orgid " +
            "WHERE organization.orgid=#{orgid} and adlog.adstart > #{date}")
    Page<AdLogDto> selectWaitPutAdList(Page<AdLogDto> page,@Param("orgid")Integer orgid, @Param("date")Date date);

    /**
     * 根据orgid三表联查已完成广告数量
     * @param orgid
     * @return
     */
    @Select("SELECT COUNT(adid) FROM adlog " +
            "LEFT OUTER JOIN  course on adlog.adcourseid=course.courseid  " +
            "LEFT OUTER JOIN  organization on course.courseorgid=organization.orgid " +
            "WHERE organization.orgid=#{orgid} and adlog.adend < #{date}")
    Integer selectClosedAdNum(@Param("orgid")Integer orgid, @Param("date")Date date);

    /**
     * 根据orgid三表联查已完成广告列表
     * @param
     * @return
     */
    @Select("SELECT DISTINCT adlog.adid,adlog.placeid,adlog.adimg,adlog.adcourseid,adlog.adlink,adlog.adstart,adlog.adend,adlog.adprice,adlog.adsumprice,adlog.adsort,adlog.adstate,organization.orgname,course.coursetitle FROM adlog " +
            "LEFT OUTER JOIN  course on adlog.adcourseid=course.courseid  " +
            "LEFT OUTER JOIN  organization on course.courseorgid=organization.orgid " +
            "WHERE organization.orgid=#{orgid} and adlog.adend < #{date}")
    Page<AdLogDto> selectClosedAdList(Page<AdLogDto> page,@Param("orgid")Integer orgid, @Param("date")Date date);

    /**
     * 根据orgid三表联查广告总金额
     * @param
     * @return
     */
    @Select("SELECT SUM(adlog.adsumprice) FROM adlog " +
            "LEFT OUTER JOIN  course on adlog.adcourseid=course.courseid  " +
            "LEFT OUTER JOIN  organization on course.courseorgid=organization.orgid " +
            "WHERE organization.orgid=#{orgid}")
    Double selectAdRevenue(@Param("orgid")Integer orgid);


    /**
     * 根据广告状态查询广告列表
     * 1--正常
     * 2--未上架
     * 3--已下架
     * @param page
     * @param adstate
     * @return
     */
    @Select("SELECT adlog.adid,adlog.placeid,adlog.adimg,adlog.adcourseid,adlog.adlink,adlog.adstart,adlog.adend,adlog.adprice,adlog.adsumprice,adlog.adsort,adlog.adstate,course.coursetitle FROM adlog " +
            "LEFT OUTER JOIN course " +
            "on adlog.adcourseid=course.courseid" +
            " where adlog.adstate=#{adstate}")
    Page<AdLogDto> selectAdList(Page<AdLogDto> page,@Param("adstate") Integer adstate);

    /**
     * 根据广告id和广告状态查询广告列表
     * @param page
     * @param adstate
     * @param placeid
     * @return
     */
    @Select("SELECT adlog.adid,adlog.placeid,adlog.adimg,adlog.adcourseid,adlog.adlink,adlog.adstart,adlog.adend,adlog.adprice,adlog.adsumprice,adlog.adsort,adlog.adstate,course.coursetitle,adplace.placename "+
            "FROM adplace " +
            "LEFT OUTER  JOIN adlog ON adlog.placeid = adplace.placeid " +
            "LEFT OUTER  JOIN course ON course.courseid = adlog.adcourseid " +
            "WHERE adlog.placeid=#{placeid} and adstate=#{adstate}")
    Page<AdLogDto> selectAdListByPlaceId(Page<AdLogDto> page, @Param("adstate") Integer adstate, @Param("placeid") Long placeid);

    /**
     * 根据广告id和广告状态查询广告列表
     * @param page
     * @param adstate
     * @param orgid
     * @return
     */
    @Select("SELECT DISTINCT adlog.adid,adlog.placeid,adlog.adimg,adlog.adcourseid,adlog.adlink,adlog.adstart,adlog.adend,adlog.adprice,adlog.adsumprice,adlog.adsort,adlog.adstate,organization.orgname,course.coursetitle FROM adlog " +
            "LEFT OUTER JOIN  course on adlog.adcourseid=course.courseid  " +
            "LEFT OUTER JOIN  organization on course.courseorgid=organization.orgid " +
            "WHERE organization.orgid=#{orgid} and adlog.adstate=#{adstate}")
    Page<AdLogDto> selectAdListByOrgId(Page<AdLogDto> page,@Param("adstate")  Integer adstate, @Param("orgid") Integer orgid);
}
